package com.wyt.common.sqljdbc;


import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;

public class SqlTemplateUtils {
    private static Logger logger = LoggerFactory.getLogger(SqlTemplateUtils.class);

    /**
     * 获取插入语句
     */
    public static String getInsertSql(String table, String[] fields, Object[] values) {
        String sql = "insert into " + table + "(" + transFields(fields) + ")" + " values(" + transValues(values) + ")";
        logger.info("执行插入sql:{}", sql);
        return sql;
    }

    /**
     * 获取更新插入语句
     */
    public static String getUpsertSql(String table, String[] fields, Object[] values) {
        String sql = "insert into " + table + "(" + transFields(fields) + ")" + " values(" + transValues(values) + ")";
        StringBuffer sb = new StringBuffer();
        sb.append(sql).append(" on duplicate key update ");
        for (int i = 0; i < fields.length; i++)
            sb.append(fields[i]).append(" = values(").append(fields[i]).append(")").append(",");
        sb.deleteCharAt(sb.length() - 1);
        logger.info("执行插入sql:{}", sb);
        return sb.toString();
    }

    /**
     * 获取更新语句
     * 第一个元素是更新的主键
     */
    public static String getUpdateSql(String table, String mark, String[] fields, Object[] values) {
        String sql = "update " + table + " set ";
        Integer markIndex = 0;
        for (int i = 0; i < fields.length; i++) {
            if (mark.equalsIgnoreCase(fields[i])) {
                markIndex = i;
                continue;
            }
            Object tmp = values[i];
            if (tmp instanceof Number) {
                sql = sql + fields[i] + " = " + tmp + ",";
            } else {
                sql = sql + fields[i] + " = '" + tmp + "',";
            }
        }

        Object tt = values[markIndex];
        if (tt instanceof Number) {
            sql = sql.substring(0, sql.length() - 1) + " where " + fields[markIndex] + " = " + tt;
        } else {
            sql = sql.substring(0, sql.length() - 1) + " where " + fields[markIndex] + " = '" + tt + "'";
        }
        logger.info("执行更新sql:{}", sql);
        return sql;
    }

    public static String getDeleteSql(String table, String[] fields, Object[] values) {
        String sql = "delete from " + table + " where 1=1 ";
        if (fields != null && fields.length > 0) {
            for (int i = 0; i < fields.length; i++) {
                Object tmp = values[i];
                if (tmp instanceof Number) {
                    sql = sql + "and" + fields[i] + " = " + tmp + ",";
                } else {
                    sql = sql + "and" + fields[i] + " = '" + tmp + "',";
                }
            }
        }
        logger.info("获取删除sql:{}", sql);
        return sql;
    }

    /**
     * 拼接字段
     */
    private static String transFields(String[] fields) {
        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < fields.length; i++) sb.append(fields[i]).append(",");
        sb.deleteCharAt(sb.length() - 1);
        return sb.toString();
    }

    /**
     * 拼接值
     */
    private static String transValues(Object[] values) {
        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < values.length; i++) {
            Object tmp = values[i];
            if (tmp instanceof Number) {
                sb.append(tmp).append(",");
            } else {
                sb.append("'").append(tmp).append("'").append(",");
            }
        }
        sb.deleteCharAt(sb.length() - 1);
        return sb.toString();
    }


    /**
     * 获取建表语句（默认自带自增id）
     *
     * @param table  新建库名.表名
     * @param fields 名称、类型、大小、默认值、注释
     */
    public static String getCreateTableSql(String table, String[][] fields) {
        StringBuffer sb = new StringBuffer();
        sb.append("create table ").append(table).append("(");
        sb.append("id int not null AUTO_INCREMENT COMMENT '自增主键'").append(",");
        for (int i = 0; i < fields.length; i++) {
            sb.append(fields[i][0]).append(" ")
                    .append(getType(fields[i][1], fields[i][2])).append(" ")
                    .append(getDefault(fields[i][1], fields[i][3])).append(" ")
                    .append(getComment(fields[i][4])).append(",");
        }
        sb.append("primary key('id')");
        sb.append(")");
        sb.append("ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC");
        logger.info("执行建表sql:{}", sb);
        return sb.toString();
    }

    public static List<String> typeList = Arrays.asList("varchar", "int", "bigint");

    private static String getType(String type, String length) {
        switch (type.toLowerCase()) {
            case "varchar":
                if (StringUtils.isBlank(length)) length = "255";
                break;
            case "int":
                if (StringUtils.isBlank(length)) length = "11";
                break;
            case "bigint":
                if (StringUtils.isBlank(length)) length = "19";
                break;
            case "double":
                if (StringUtils.isBlank(length)) length = "17,14"; //double类型的m,n类型长度，由前端输入
                break;
            default:
                throw new RuntimeException("暂不支持" + type + "类型字段，请更新版本！");
        }
        return type + "(" + length + ")";
    }

    private static String getDefault(String type, String value) {
        switch (type.toLowerCase()) {
            case "varchar":
                return "default '" + value + "'";
            case "int":
            case "bigint":
            case "double":
                return "default " + value;
            default:
                throw new RuntimeException("暂不支持" + type + "类型字段，请更新版本！");
        }
    }

    private static String getComment(String comment) {
        if (StringUtils.isNotBlank(comment)) return "comment '" + comment + "'";
        return "";
    }





    public static void main(String[] args) throws SQLException {
        Connection conn = null;
        // SQL语句中的参数值，使用？占位符替代
        String sql = "select * from user where username = ? and password = ?";
        // 通过Connection对象获取，并传入对应的sql语句
        PreparedStatement pstmt = conn.prepareStatement(sql);


    }


}
